﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MasMembers.DAL
{
    
    public class CoursesEntity
    {
        public  Int32 C_Id { get; set; }
        public  Int32 Mem_Id { get; set; }
        public  String Course_Type { get; set; }
        public  String Course_Name { get; set; }
        public  String Course_Grade { get; set; }
        public  String Course_Year { get; set; }
        public  String Course_From { get; set; }

    }
   public class Courses
    {

       private static string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='MasMembers.accdb';Jet OLEDB:Database Password=Yarabe11";
        private static String cmdText = "";

        public static bool Add(CoursesEntity s)
        {
            bool flag = true;
   
            cmdText = "Insert into Courses (Mem_Id,Course_Type,Course_Name,Course_Grade,Course_Year,Course_From) values (?,?,?,?,?,?)";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);


            cmd.Parameters.AddWithValue("@Mem_Id", s.Mem_Id);
            cmd.Parameters.AddWithValue("@Course_Type", s.Course_Type);
            cmd.Parameters.AddWithValue("@Course_Name", s.Course_Name);
            cmd.Parameters.AddWithValue("@Course_Grade", s.Course_Grade);
            cmd.Parameters.AddWithValue("@Course_Year", s.Course_Year);
            cmd.Parameters.AddWithValue("@Course_From", s.Course_From);

            try
            {
                Con.Open();
                int Num = cmd.ExecuteNonQuery();
                if (Num == 0)
                    flag = false;
            }

            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;


        }


        public static bool Update(CoursesEntity s)
        {
            bool flag = true;     

            cmdText = "Update Courses set Mem_Id=?, Course_Type =?, Course_Name=?,  Course_Grade=?, Course_Year=?, Course_From =?  WHERE Id= ? ";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
            cmd.Parameters.AddWithValue("@Mem_Id", s.Mem_Id);
            cmd.Parameters.AddWithValue("@Course_Type", s.Course_Type);
            cmd.Parameters.AddWithValue("@Course_Name", s.Course_Name);
            cmd.Parameters.AddWithValue("@Course_Grade", s.Course_Grade);
            cmd.Parameters.AddWithValue("@Course_Year", s.Course_Year);
            cmd.Parameters.AddWithValue("@Course_From", s.Course_From);
            cmd.Parameters.AddWithValue("@Id", s.C_Id);
          

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }


        public static bool Delete(Int32 s)
        {
            bool flag = true;

            cmdText = "Delete from Courses where Id = ?";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);

            cmd.Parameters.AddWithValue("@Id", s);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

        public static List<CoursesEntity> GetCourseByMemID(Int32 Id)
        {
            {
                CoursesEntity x = null;
                List<CoursesEntity> CoursesList = new List<CoursesEntity>();

                cmdText = "Select * from Courses where Mem_Id=? ";

                OleDbConnection Con = new OleDbConnection(conString);
                OleDbCommand cmd = new OleDbCommand(cmdText, Con);
                cmd.Parameters.AddWithValue("@Mem_Id", Id);

                try
                {
                    Con.Open();

                    OleDbDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        x = FillEntityFromReader(reader);
                        CoursesList.Add(x);
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return CoursesList;
            }
        }
   
     
        public static CoursesEntity  GetCourseID(CoursesEntity e)
        {
          
            cmdText = "Select * from Courses  where Mem_Id=?, Course_Type=?, Course_Name=?,  Course_Grade=?, Course_Year=?, Course_From=?";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
            cmd.Parameters.AddWithValue("@Mem_Id", e.Mem_Id);
            cmd.Parameters.AddWithValue("@Course_Type", e.Course_Type);
            cmd.Parameters.AddWithValue("@Course_Name", e.Course_Name);
            cmd.Parameters.AddWithValue("@Course_Grade", e.Course_Grade);
            cmd.Parameters.AddWithValue("@Course_Year", e.Course_Year);
            cmd.Parameters.AddWithValue("@Course_From", e.Course_From);
            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    e.C_Id = Int16.Parse(reader["Id"].ToString());
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(" خطأ عند تحديد رقم العميل مطلوبة من قاعدة بيانات العميل ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
               
            }

            return e; 
                
                
        }
     
        public static List<CoursesEntity> GetAllCourses()
        {
            {
                CoursesEntity x = null;
                List<CoursesEntity> CourseList = new List<CoursesEntity>();

                cmdText = "Select * from Courses   ";

                OleDbConnection Con = new OleDbConnection(conString);
                OleDbCommand cmd = new OleDbCommand(cmdText, Con);



                try
                {
                    Con.Open();

                    OleDbDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        x = FillEntityFromReader(reader);
                        CourseList.Add(x);
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد كل البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return CourseList;
            }
        }

        private static CoursesEntity FillEntityFromReader(OleDbDataReader reader)
        {
          
            CoursesEntity x = new CoursesEntity();
            x.C_Id = Int32.Parse(reader["Id"].ToString());
            if (reader["Mem_Id"] != null)
                x.Mem_Id =Int32.Parse( reader["Mem_Id"].ToString());
            if (reader["Course_Type"] != null)
                x.Course_Type = reader["Course_Type"].ToString();
            if ((reader["Course_Name"] != null))
                x.Course_Name = reader["Course_Name"].ToString();
            if ((reader["Course_Grade"] != null))
                x.Course_Grade = reader["Course_Grade"].ToString();
            if (reader["Course_Year"] != null)
                x.Course_Year = reader["Course_Year"].ToString();
            if (reader["Course_From"] != null)
                x.Course_From = reader["Course_From"].ToString();
       
            return x;
        }
    }
    
    }

